package com.jy.appmarket.download;

import java.io.File;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.text.TextUtils;
import android.util.Log;

import com.jy.appmarket.utils.LogUtil;

public final class DownloadDatabase {
	private static final String TAG = "DownloadDatabase";

	private static final String DB_NAME = "download";
	private static final String DB_TABLE = "downloads";
	private static final int DB_VERSION = 2;

	public static final String COL_ID = "_id";// long
	public static final String COL_DL_ID = "dl_id";// String
	public static final String COL_DL_URL = "dl_url";// String
	public static final String COL_NAME = "name";// String
	public static final String COL_STATUS = "status";// int
	public static final String COL_CREATE_TIME = "create_time";// long
	public static final String COL_LAST_MODIFICATION = "last_modification";// long
	public static final String COL_DOWNLOADED_SIZE = "downloaded_size";// long
	public static final String COL_TOTAL_SIZE = "total_size";// long
	public static final String COL_RETRY_COUNT = "retry_count";// int

	public static final String COL_PKG_NAME = "pkg_name";// String
	public static final String COL_VERSION_NAME = "version_name";// String
	public static final String COL_VERSION_CODE = "version_code";// String
	public static final String COL_MD5 = "md5";// String

	public static final String COL_AUTO_INSTALL = "auto_install";// int

	public static final String COL_ICON_URL = "icon_url";// String
	public static final String COL_SOURCE = "source";// String
	public static final String COL_PRIORITY = "priority";// int优先级
	public static final String COL_SHOULD_REMOVE = "should_remove";
	public static final String COL_HIDDEN = "hidden";

	private static final String CREATE_SQL = "CREATE TABLE " + DB_TABLE + " (" + COL_ID + " BIGINT PRIMARY KEY,"
			+ COL_DL_ID + " TEXT UNIQUE," + COL_DL_URL + " TEXT," + COL_NAME + " TEXT," + COL_STATUS + " INTEGER,"
			+ COL_CREATE_TIME + " BIGINT," + COL_LAST_MODIFICATION + " BIGINT," + COL_DOWNLOADED_SIZE + " BIGINT,"
			+ COL_TOTAL_SIZE + " BIGINT," + COL_RETRY_COUNT + " INTEGER," + COL_PKG_NAME + " TEXT," + COL_VERSION_NAME
			+ " TEXT," + COL_VERSION_CODE + " INTEGER," + COL_MD5 + " TEXT," + COL_AUTO_INSTALL + " INTEGER,"
			+ COL_ICON_URL + " TEXT," + COL_SOURCE + " TEXT," + COL_SHOULD_REMOVE + " INTEGER," + COL_HIDDEN
			+ " INTEGER," + COL_PRIORITY + " INTEGER);";

	private static class DatabaseHelper extends SQLiteOpenHelper {

		public DatabaseHelper(Context context) {
			super(context, DB_NAME, null, DB_VERSION);
		}

		@Override
		public void onCreate(SQLiteDatabase db) {
			db.execSQL(CREATE_SQL);
		}

		@Override
		public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
			if (newVersion == 2) {
				correctDownload(db);
				updateDownloadId(db);
				oldVersion++;
			}
			// if(newVersion==3){
			// // upgrade of 3
			// oldVersion++;
			// }
		}

		/**
		 * 去掉包名相同的下载任务，仅保留最高版本的任务
		 */
		private void correctDownload(SQLiteDatabase db) {
			Cursor cur = null;

			try {
				cur = db.query(DB_TABLE, null, null, null, null, null,
						COL_PKG_NAME + " DESC, " + COL_VERSION_CODE + " DESC");

				int colDlId = cur.getColumnIndex(COL_DL_ID);
				int colPkgName = cur.getColumnIndex(COL_PKG_NAME);

				String pkgName = null;
				for (cur.moveToFirst(); !cur.isAfterLast(); cur.moveToNext()) {
					String dlId = cur.getString(colDlId);
					String pn = cur.getString(colPkgName);

					if (TextUtils.isEmpty(pn)) {
						db.delete(DB_TABLE, COL_DL_ID + "=?", new String[] { dlId });
						removeDownloadFile(dlId);
						continue;
					}

					if (pn.equals(pkgName)) {
						db.delete(DB_TABLE, COL_DL_ID + "=?", new String[] { dlId });
						removeDownloadFile(dlId);
					} else {
						pkgName = pn;
					}

				}
			} finally {
				if (cur != null) {
					cur.close();
				}
			}
		}

		/**
		 * 更新下载任务id
		 */
		private void updateDownloadId(SQLiteDatabase db) {
			Cursor cur = null;

			try {
				cur = db.query(DB_TABLE, null, null, null, null, null, null);
				int colDlId = cur.getColumnIndex(COL_DL_ID);
				int colPkgName = cur.getColumnIndex(COL_PKG_NAME);
				int colVC = cur.getColumnIndex(COL_VERSION_CODE);

				for (cur.moveToFirst(); !cur.isAfterLast(); cur.moveToNext()) {
					String oldDlId = cur.getString(colDlId);
					String pn = cur.getString(colPkgName);
					int vc = cur.getInt(colVC);
					String dlId = DownloadUtils.generateDownloadId(pn, vc);
					ContentValues values = new ContentValues();
					values.put(COL_DL_ID, dlId);
					int count = db.update(DB_TABLE, values, COL_DL_ID + "=?", new String[] { String.valueOf(oldDlId) });
					LogUtil.d(TAG, "update " + oldDlId + " " + COL_DL_ID + " from " + oldDlId + " to " + dlId
							+ " status:" + count);
					renameDownloadFile(oldDlId, dlId);
				}
			} finally {
				if (cur != null) {
					cur.close();
				}
			}
		}

		private void removeDownloadFile(String dlId) {
			new File(DownloadUtils.getDownloadApkTmpFilePathById(dlId)).delete();
			new File(DownloadUtils.getDownloadApkFilePathById(dlId)).delete();
		}

		private void renameDownloadFile(String oldId, String newId) {
			File oldFile = null;
			File newFile = null;
			boolean status = false;
			// download tmp file
			oldFile = new File(DownloadUtils.getDownloadApkTmpFilePathById(oldId));
			if (oldFile.exists()) {
				newFile = new File(DownloadUtils.getDownloadApkTmpFilePathById(newId));
				status = oldFile.renameTo(newFile);
				LogUtil.d(TAG, "rename tmp:" + oldFile + " to " + newFile + " status:" + status);
			}

			// downloaded file
			oldFile = new File(DownloadUtils.getDownloadApkFilePathById(oldId));
			if (oldFile.exists()) {
				newFile = new File(DownloadUtils.getDownloadApkFilePathById(newId));
				status = oldFile.renameTo(newFile);
				LogUtil.d(TAG, "rename tmp:" + oldFile + " to " + newFile + " status:" + status);
			}
		}
	}

	private static DownloadDatabase sInstance = null;
	private DatabaseHelper mHelper = null;

	private DownloadDatabase(Context context) {
		mHelper = new DatabaseHelper(context);
	}

	static DownloadDatabase getInstance(Context context) {
		if (sInstance == null) {
			synchronized (DownloadDatabase.class) {
				if (sInstance == null) {
					sInstance = new DownloadDatabase(context);
				}
			}
		}
		return sInstance;
	}

	// TODO 这里可能产生竞争问题，A线程拿到游标读取的同时线程写入数据，需要修改
	public synchronized Cursor query(String selection, String[] selectionArgs, String orderBy) {
		SQLiteDatabase db = mHelper.getReadableDatabase();
		return db.query(DB_TABLE, null, selection, selectionArgs, null, null, orderBy);
	}

	public synchronized boolean exist(String dlId) {
		Cursor cur = query(COL_DL_ID + "='" + dlId + "'", null, null);
		boolean exist = cur.moveToFirst();
		cur.close();
		return exist;
	}

	public synchronized long insert(ContentValues cv) {
		SQLiteDatabase db = mHelper.getWritableDatabase();
		long now = System.currentTimeMillis();
		cv.put(COL_CREATE_TIME, now);
		cv.put(COL_LAST_MODIFICATION, now);
		return db.insert(DB_TABLE, null, cv);
	}

	public synchronized int update(String id, ContentValues cv) {
		SQLiteDatabase db = mHelper.getWritableDatabase();
		long now = System.currentTimeMillis();
		cv.put(COL_DL_ID, id);
		cv.put(COL_LAST_MODIFICATION, now);
		return db.update(DB_TABLE, cv, COL_DL_ID + "='" + id + "'", null);
	}

	public synchronized int deleteById(String id) {
		SQLiteDatabase db = mHelper.getWritableDatabase();
		return db.delete(DB_TABLE, COL_DL_ID + "='" + id + "'", null);
	}

	public synchronized int deleteByStatus(int status) {
		SQLiteDatabase db = mHelper.getWritableDatabase();
		return db.delete(DB_TABLE, COL_STATUS + "=" + status, null);
	}

	public static DownloadItem buildFromCursor(Cursor cur) {
		if (cur == null) {
			Log.w(TAG, "cursor is null");
			return null;
		}
		DownloadItem di = new DownloadItem();
		di.set_id(cur.getLong(cur.getColumnIndex(COL_ID)));
		di.setId(cur.getString(cur.getColumnIndex(COL_DL_ID)));
		di.setDlUrl(cur.getString(cur.getColumnIndex(COL_DL_URL)));
		di.setName(cur.getString(cur.getColumnIndex(COL_NAME)));
		di.setStatus(cur.getInt(cur.getColumnIndex(COL_STATUS)));
		di.setCreateTime(cur.getLong(cur.getColumnIndex(COL_CREATE_TIME)));
		di.setLastModification(cur.getLong(cur.getColumnIndex(COL_LAST_MODIFICATION)));
		di.setDownloadedSize(cur.getLong(cur.getColumnIndex(COL_DOWNLOADED_SIZE)));
		di.setTotalSize(cur.getLong(cur.getColumnIndex(COL_TOTAL_SIZE)));
		di.setRetryCount(cur.getInt(cur.getColumnIndex(COL_RETRY_COUNT)));
		di.setPkgName(cur.getString(cur.getColumnIndex(COL_PKG_NAME)));
		di.setVersionName(cur.getString(cur.getColumnIndex(COL_VERSION_NAME)));
		di.setVersionCode(cur.getInt(cur.getColumnIndex(COL_VERSION_CODE)));
		di.setMd5(cur.getString(cur.getColumnIndex(COL_MD5)));
		di.setAutoInstall(cur.getInt(cur.getColumnIndex(COL_AUTO_INSTALL)) == 1);
		di.setIconUrl(cur.getString(cur.getColumnIndex(COL_ICON_URL)));
		di.setSource(cur.getString(cur.getColumnIndex(COL_SOURCE)));
		di.setPriority(cur.getInt(cur.getColumnIndex(COL_PRIORITY)));
		di.setShouldRemove(cur.getInt(cur.getColumnIndex(COL_SHOULD_REMOVE)) == 1);
		di.setHidden(cur.getInt(cur.getColumnIndex(COL_HIDDEN)) == 1);
		return di;
	}
}
